﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class BookBorrowDao
    {
        public bool DeleteAdmin(SqlConnection conn, int borrowId)
        {
            string commandText = "DELETE FROM [dbo].[Book_Borrow] WHERE [BorrowId] = @BorrowId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BorrowId", SqlDbType.Int) };
            commandParameters[0].Value = borrowId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool DeleteMy(SqlConnection conn, int borrowId, string userId)
        {
            string commandText = "IF EXISTS(SELECT [BorrowId] FROM [dbo].[Book_Borrow] WHERE [BorrowId] = @BorrowId AND [BUserId] = @UserId AND [Status] IN(0,2) ) BEGIN DELETE FROM [dbo].[Book_Borrow] WHERE [BorrowId] = @BorrowId AND [BUserId] = @UserId END ELSE BEGIN UPDATE [dbo].[Book_Borrow] SET [DelFlag] = 1 WHERE [BorrowId] = @BorrowId AND [BUserId] = @UserId END";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BorrowId", SqlDbType.Int);
            commandParameters[0].Value = borrowId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, BookBorrowData data)
        {
            string commandText = "INSERT INTO [dbo].[Book_Borrow] ( [BUserId], [BookId], [BorrowDate], [BorrowRemark], [RUserId], [ReturnDate], [BookStatus], [RealReturnDate], [DelFlag], [Status], [RegFlag], [WhoConfim] ) VALUES ( @BUserId, @BookId, @BorrowDate, @BorrowRemark, @RUserId, @ReturnDate, @BookStatus, @RealReturnDate, @DelFlag, @Status, @RegFlag, @WhoConfim ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[12];
            commandParameters[0] = new SqlParameter("@BUserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.BUserId;
            commandParameters[1] = new SqlParameter("@BookId", SqlDbType.Int);
            commandParameters[1].Value = data.BookId;
            commandParameters[2] = new SqlParameter("@BorrowDate", SqlDbType.DateTime);
            if (data.BorrowDateIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.BorrowDate;
            }
            commandParameters[3] = new SqlParameter("@BorrowRemark", SqlDbType.NText);
            if (data.BorrowRemarkIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.BorrowRemark;
            }
            commandParameters[4] = new SqlParameter("@RUserId", SqlDbType.NVarChar, 20);
            if (data.RUserIdIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.RUserId;
            }
            commandParameters[5] = new SqlParameter("@ReturnDate", SqlDbType.DateTime);
            if (data.ReturnDateIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.ReturnDate;
            }
            commandParameters[6] = new SqlParameter("@BookStatus", SqlDbType.Int);
            commandParameters[6].Value = data.BookStatus;
            commandParameters[7] = new SqlParameter("@RealReturnDate", SqlDbType.DateTime);
            if (data.RealReturnDateIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.RealReturnDate;
            }
            commandParameters[8] = new SqlParameter("@DelFlag", SqlDbType.Int);
            commandParameters[8].Value = data.DelFlag;
            commandParameters[9] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[9].Value = data.Status;
            commandParameters[10] = new SqlParameter("@RegFlag", SqlDbType.Int);
            if (data.RegFlagIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.RegFlag;
            }
            commandParameters[11] = new SqlParameter("@WhoConfim", SqlDbType.NVarChar, 20);
            if (data.WhoConfimIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.WhoConfim;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool Manage(SqlConnection conn, int borrowId, int status, int bookStatus)
        {
            string commandText = "UPDATE [dbo].[Book_Borrow] SET [Status] = @Status, [BookStatus] = @BookStatus WHERE [BorrowId] = @BorrowId IF @BookStatus =3 UPDATE [dbo].[Book_Borrow] SET [RealReturnDate] = GETDATE() WHERE [BorrowId] = @BorrowId";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@BorrowId", SqlDbType.Int);
            commandParameters[0].Value = borrowId;
            commandParameters[1] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[1].Value = status;
            commandParameters[2] = new SqlParameter("@BookStatus", SqlDbType.Int);
            commandParameters[2].Value = bookStatus;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool ReturnMy(SqlConnection conn, int borrowId, string userId)
        {
            string commandText = "IF EXISTS(SELECT [BorrowId] FROM [dbo].[Book_Borrow] WHERE [BorrowId] = @BorrowId AND [BUserId] = @UserId AND [Status]=1 AND [BookStatus] IN(0,2)) UPDATE [dbo].[Book_Borrow] SET [RUserId] = @UserId, [BookStatus] = 1, [RealReturnDate] = GETDATE() WHERE [BorrowId] = @BorrowId AND [BUserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@BorrowId", SqlDbType.Int);
            commandParameters[0].Value = borrowId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public DataTable SelectAdmin0(SqlConnection conn)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND ([Book_Borrow].[Status]=1 AND [Book_Borrow].[BookStatus] IN(4))";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectAdmin1(SqlConnection conn)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND [Book_Borrow].[Status]=0";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectAdmin2(SqlConnection conn)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND ([Book_Borrow].[Status]=1 AND [Book_Borrow].[BookStatus] IN(1))";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectByBorrowId(SqlConnection conn, int borrowId)
        {
            string commandText = "SELECT [BorrowId], [BUserId], [BookId], [BorrowDate], [BorrowRemark], [RUserId], [ReturnDate], [BookStatus], [RealReturnDate], [DelFlag], [Status], [RegFlag], [WhoConfim] FROM [dbo].[Book_Borrow] WHERE [BorrowId] = @BorrowId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BorrowId", SqlDbType.Int) };
            commandParameters[0].Value = borrowId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectMy0(SqlConnection conn, string userId)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BUserId] = @UserId AND [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND ([Book_Borrow].[Status]=0 OR ([Book_Borrow].[Status]=1 AND [Book_Borrow].[BookStatus] IN (1))) AND [DelFlag] = 0";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectMy1(SqlConnection conn, string userId)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BUserId] = @UserId AND [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND ([Book_Borrow].[Status]=1 AND [Book_Borrow].[BookStatus] IN(0,3)) AND [DelFlag] = 0";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }

        public DataTable SelectMy2(SqlConnection conn, string userId)
        {
            string commandText = "SELECT [Book_Borrow].*, [Book_Info].[BookName], [Book_Info].[BookNo], [Users].[UserName] FROM [dbo].[Book_Borrow], [dbo].[Book_Info], [dbo].[Users] WHERE [Book_Borrow].[BUserId] = @UserId AND [Book_Borrow].[BookId] = [Book_Info].[BookId] AND [Book_Borrow].[BUserId] = [Users].[UserId] AND ([Book_Borrow].[Status]=2 OR ([Book_Borrow].[Status]=1 AND [Book_Borrow].[BookStatus] IN (2))) AND [DelFlag] = 0";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Borrow";
            return set.Tables[0];
        }
    }
}

